package com.winning.pmph.utils;

import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Objects;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.winning.pmph.vo.ExcelDb;

/**
 * @author 刘亚飞
 * @Description
 * @create 2020-10-15 16:26
 */
public class ExcelUtil {
    public static void excelDownload(List<ExcelDb> list, OutputStream out) {
        Workbook workbook = new XSSFWorkbook();
        for (int m = 1; m <= list.size(); m++) {
            ExcelDb exceldb = list.get(m - 1);
            String sheetName = exceldb.getSheetName() == null ? "Sheet" + m : exceldb.getSheetName();
            //String title = exceldb.getTitle();
            String[] headers = exceldb.getHeaders();
            List<String[]> data = exceldb.getData();
            Sheet sheet = workbook.createSheet(sheetName);
            Row row1 = sheet.createRow(0);
            for (int i = 0; i < headers.length; i++) {
                Cell cell1 = row1.createCell(i);
                cell1.setCellValue(headers[i]);
                cell1.setCellStyle(setStyleHead(workbook));
            }
            for (int i = 0; i < data.size(); i++) {
                Row row2 = sheet.createRow(i + 1);
                String[] strings = data.get(i);
                for (int j = 0; j < strings.length; j++) {
                    Cell cell2 = row2.createCell(j);
                    cell2.setCellValue(strings[j]);
                }
            }
        }
        try {
             workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    /*private static CellStyle setStyleTitle(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 16);
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }*/

    private static CellStyle setStyleHead(Workbook workbook) {
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 10);
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        return cellStyle;
    }

    public static String superOrSubScript2007(Cell cell, Workbook book) {
        XSSFWorkbook workbook = null;
        XSSFFont font = null;
        XSSFRichTextString rts = null;
        XSSFCellStyle style = null;
        int runIndex = 0;
        int runLength = 0;
        String value = "";
        // 处理上下标
        workbook = (XSSFWorkbook) book;
        if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
            cell.setCellValue((cell.getNumericCellValue() + "").substring(0,
                    (cell.getNumericCellValue() + "").indexOf(".")));
        }
        rts = (XSSFRichTextString) cell.getRichStringCellValue();
        style = (XSSFCellStyle) cell.getCellStyle();
        font = style.getFont();

        if (rts.numFormattingRuns() > 1) {
            for (int k = 0; k < rts.numFormattingRuns(); k++) {
                runLength = rts.getLengthOfFormattingRun(k);
                runIndex = rts.getIndexOfFormattingRun(k);
                String temp = rts.toString().substring(runIndex,
                        (runIndex + runLength));
                font = rts.getFontOfFormattingRun(k);
                if (Objects.isNull(font)) {
                    font = style.getFont();
                }
                if (font.getTypeOffset() == XSSFFont.SS_SUPER) {
                    temp = "<sup>" + temp + "</sup>";
                }
                if (font.getTypeOffset() == XSSFFont.SS_SUB) {
                    temp = "<sub>" + temp + "</sub>";
                }
                value += temp;
            }
            return value;
        }
        return cell.getRichStringCellValue().getString();
    }
    
	public static String getCellStr(Cell cell) {
		if(cell==null) {
			return "";
		}
		if (cell.getCellTypeEnum() == CellType.STRING) {
			return cell.getStringCellValue();
		} else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
			double d = cell.getNumericCellValue();
			return Double.valueOf(d).longValue() + "";
		}
		return cell.getStringCellValue();
	}
}
